import Database from 'better-sqlite3';
import path from 'path';
import * as fs from 'fs';

const DB_PATH = path.join(process.cwd(), 'data', 'delivery-management.db');
const DATA_DIR = path.join(process.cwd(), 'excel', '06-21');
const db = new Database(DB_PATH);

interface FieldMetadata {
  id?: number;
  table_name: string;           // 参数表名（如 table_c18）
  table_code: string;           // 表代码（如 BAS012）
  table_description: string;    // 表描述（如 离心泵基础参数表）
  field_sequence: number;       // 字段序号
  field_name: string;           // 中文名称
  field_column: string;         // 数据库列名（字段名的安全版本）
  example_description: string;  // 示例或属性说明
  data_type: string;            // 数据类型
  measurement_class: string;    // 计量类
  data_source: string;          // 数据来源
  is_required: boolean;         // 是否必填
  created_at?: string;
  updated_at?: string;
}

interface TableDefinition {
  tableName: string;
  tableCode: string;
  description: string;
  fields: Array<{
    sequence: string;
    name: string;
    example: string;
    dataType: string;
    measurementClass: string;
    dataSource: string;
  }>;
}

function createFieldMetadataTable() {
  const createTableSQL = `
    CREATE TABLE IF NOT EXISTS parameter_field_metadata (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      table_name TEXT NOT NULL,
      table_code TEXT NOT NULL,
      table_description TEXT NOT NULL,
      field_sequence INTEGER NOT NULL,
      field_name TEXT NOT NULL,
      field_column TEXT NOT NULL,
      example_description TEXT,
      data_type TEXT NOT NULL,
      measurement_class TEXT,
      data_source TEXT,
      is_required BOOLEAN DEFAULT 0,
      created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
      UNIQUE(table_name, field_name)
    )
  `;
  
  db.exec(createTableSQL);
  
  // 创建索引
  db.exec(`
    CREATE INDEX IF NOT EXISTS idx_parameter_field_metadata_table_name 
    ON parameter_field_metadata(table_name);
    CREATE INDEX IF NOT EXISTS idx_parameter_field_metadata_table_code 
    ON parameter_field_metadata(table_code);
    CREATE INDEX IF NOT EXISTS idx_parameter_field_metadata_field_sequence 
    ON parameter_field_metadata(table_name, field_sequence);
  `);
  
  console.log('✅ 创建字段元数据表');
}

function parseCSVFile(filename: string): TableDefinition[] {
  const filePath = path.join(DATA_DIR, filename);
  if (!fs.existsSync(filePath)) {
    console.warn(`文件不存在: ${filePath}`);
    return [];
  }

  let contentBuffer = fs.readFileSync(filePath);
  // 处理 BOM
  if (contentBuffer[0] === 0xEF && contentBuffer[1] === 0xBB && contentBuffer[2] === 0xBF) {
    contentBuffer = contentBuffer.subarray(3);
  }

  const content = contentBuffer.toString('utf-8');
  const lines = content.split('\n').map(line => line.trim()).filter(line => line);
  
  const tables: TableDefinition[] = [];
  let currentTable: TableDefinition | null = null;
  let expectingHeader = true;

  for (let i = 0; i < lines.length; i++) {
    const line = lines[i];
    const columns = line.split(',').map(col => col.trim());

    // 检查是否是表头行（包含"表 C."）
    if (line.includes('表 C.') || line.includes('表C.')) {
      // 保存之前的表
      if (currentTable) {
        tables.push(currentTable);
      }

      // 解析表名信息
      const tableInfo = columns[0];
      const match = tableInfo.match(/表\s*C\.?(\d+(?:-\d+)?)\s+([A-Z]+\d+(?:-\d+)?)\s+(.+)/);
      
      if (match) {
        const [, tableNumber, tableCode, description] = match;
        const sanitizedTableNumber = tableNumber.replace('-', '_');
        currentTable = {
          tableName: `table_c${sanitizedTableNumber}`,
          tableCode: tableCode,
          description: description,
          fields: []
        };
        expectingHeader = true;
      }
      continue;
    }

    // 检查是否是列名行
    if (expectingHeader && columns[0] === '序号' && columns[1] === '中文名称') {
      expectingHeader = false;
      continue;
    }

    // 处理字段定义行
    if (currentTable && !expectingHeader && columns.length >= 6) {
      const sequence = columns[0];
      const name = columns[1];
      const example = columns[2];
      const dataType = columns[3];
      const measurementClass = columns[4];
      const dataSource = columns[5];

      // 跳过空行或无效行
      if (sequence && name && sequence !== '序号' && name.trim() !== '') {
        currentTable.fields.push({
          sequence,
          name,
          example,
          dataType,
          measurementClass,
          dataSource
        });
      }
    }
  }

  // 添加最后一个表
  if (currentTable) {
    tables.push(currentTable);
  }

  return tables;
}

function sanitizeColumnName(name: string): string {
  // 将中文字段名转换为安全的数据库列名
  return name.replace(/[^\w\u4e00-\u9fa5]/g, '_');
}

function insertFieldMetadata(tableDef: TableDefinition) {
  const insertSQL = `
    INSERT OR REPLACE INTO parameter_field_metadata 
    (table_name, table_code, table_description, field_sequence, field_name, field_column, 
     example_description, data_type, measurement_class, data_source, is_required)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  `;
  
  const stmt = db.prepare(insertSQL);
  
  let insertedCount = 0;
  for (const field of tableDef.fields) {
    const fieldSequence = parseInt(field.sequence) || 0;
    const fieldColumn = sanitizeColumnName(field.name);
    const isRequired = field.dataSource === 'P' || field.example.includes('必填'); // P表示采购数据，通常是必填的
    
    stmt.run(
      tableDef.tableName,
      tableDef.tableCode,
      tableDef.description,
      fieldSequence,
      field.name,
      fieldColumn,
      field.example || '',
      field.dataType || '字符型',
      field.measurementClass || '—',
      field.dataSource || '',
      isRequired ? 1 : 0
    );
    insertedCount++;
  }
  
  console.log(`✅ 插入表 ${tableDef.tableName} 的 ${insertedCount} 个字段元数据`);
}

async function main() {
  try {
    console.log('🚀 开始创建字段元数据系统...');
    
    // 1. 创建字段元数据表
    createFieldMetadataTable();
    
    // 2. 解析CSV文件并插入字段元数据
    const files = [
      '属性表-对象命名表.csv',
      '属性表-基础参数表.csv',
      '属性表-位置信息表.csv'
    ];

    let totalFields = 0;
    
    for (const filename of files) {
      console.log(`\n📄 处理文件: ${filename}`);
      const tables = parseCSVFile(filename);
      
      console.log(`发现 ${tables.length} 个表定义`);
      
      for (const tableDef of tables) {
        insertFieldMetadata(tableDef);
        totalFields += tableDef.fields.length;
      }
    }
    
    console.log(`\n✅ 字段元数据系统创建完成!`);
    console.log(`📊 总计处理了 ${totalFields} 个字段的元数据`);
    
    // 验证数据
    const fieldCount = db.prepare('SELECT COUNT(*) as count FROM parameter_field_metadata').get() as any;
    console.log(`💾 数据库中共有 ${fieldCount.count} 个字段元数据记录`);
    
    // 显示一些示例数据
    const sampleFields = db.prepare(`
      SELECT table_name, table_description, field_name, data_type, measurement_class, data_source 
      FROM parameter_field_metadata 
      WHERE table_name = 'table_c18' 
      ORDER BY field_sequence 
      LIMIT 5
    `).all();
    
    console.log('\n📋 示例字段元数据（table_c18 - 离心泵基础参数表）:');
    sampleFields.forEach((field: any) => {
      console.log(`  ${field.field_name}: ${field.data_type} | 计量类: ${field.measurement_class} | 来源: ${field.data_source}`);
    });
    
  } catch (error) {
    console.error('❌ 创建字段元数据系统失败:', error);
    process.exit(1);
  } finally {
    db.close();
  }
}

main();
